﻿@page "/Form9"
@using System.Diagnostics.CodeAnalysis
@using BootstrapBlazor.OnlyServerZhongShun.Data.dbmodels
@using System.Data
@inject ILogger<Form9> NormalLogger
@inject AuthenticationStateProvider authenticationStateProvider
@inject ToastService ToastService
@inject IConfiguration _congifuration
@inject SqlSugarScope _SqlSugarScope
@attribute [Authorize(Roles = "超级管理员,业务总, 业务经理, 业务员")]

<HeadContent>
    <style>
        .radio-list {
            --bb-radio-item-width: 70px;
        }

        .checkbox-list {
            --bb-checkbox-item-width: 80px;
        }

        .multi-select {
            --bb-multi-select-min-height: 26px;
        }
    </style>
</HeadContent>

<div class="row g-1">
    <div class="col-6">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="查询日期" />
            <DateTimeRange @bind-Value="@NormalDateTimeRangeValue" OnConfirm="OnNormalConfirm" ShowSidebar="true" ViewMode="DatePickerViewMode.Date" IsEditable="true" DateFormat="yyyy-MM-dd">
                <TimePickerSetting ShowClockScale="true" IsAutoSwitch="false" />
            </DateTimeRange>
        </BootstrapInputGroup>
    </div>
    <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="客户" />
            <Select IsVirtualize="true" Items="客户列表Items" @bind-Value="选中客户" OnSearchTextChanged="OnSearchTextChanged" ShowSearch="true" IsClearable="true"></Select>
        </BootstrapInputGroup>
    </div>
    <div class="col-3">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="部门" />
            <MultiSelect Items="@事业部Items" @bind-Value="@选中事业部List" OnSelectedItemsChanged="@部门选择改变事件" ShowToolbar="true" />
        </BootstrapInputGroup>
    </div>
</div>
<div class="row g-1">
    <div class="col-4 col-sm-4">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="业务员" />
            <MultiSelect Items="@业务员Items" @bind-Value="@选中业务员List" OnSelectedItemsChanged="@业务员选择改变事件" ShowToolbar="true" ShowSearch="true"/>
        </BootstrapInputGroup>
    </div>
    <div class="col-4 col-sm-4">
        <RadioList TValue="string" Items="@代理商类型" @bind-Value="@选中代理商类型" />
    </div>
    <div class="col-4 col-sm-4">
        <RadioList TValue="string" Items="@发货回款类别" @bind-Value="@选中发货回款类别" />
    </div>
</div>

<div class="row g-1">
    <div class="col-2">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="养殖类别" />
            <Select IsVirtualize="true" Items="养殖类别Items" @bind-Value="选中养殖类别" ShowSearch="false" IsClearable="true"></Select>
        </BootstrapInputGroup>
    </div>
    <div class="col-8">
        <BootstrapInputGroup>
            <BootstrapInputGroupLabel DisplayText="查询列" />
            <CheckboxList TValue="string" @bind-Value="@选中查询列" Items="@查询列" ShowLabel="false" ShowLabelTooltip="true"></CheckboxList>
        </BootstrapInputGroup>
    </div>
    <div class="col-2">
        <Button OnClick="@Button_chaxun_Click" IsAsync="true" Color="Color.Primary">查询</Button>
        <Button OnClick="@Button_daochu" Color="Color.Info">导出</Button>

        </div>
</div>
<Table TItem="DynamicObject" DynamicContext="DataTableDynamicContext"
       IsBordered="true" IsMultipleSelect="false" ShowToolbar="true" ShowRefresh="false" ShowDefaultButtons="false" IsPagination="true" 
       OnSort="OnSort" ShowFooter="true" IsStriped="true" Height="500" IsFixedHeader="true" PageItemsSource="new int[]{1000,2000,3000,4000,5000,10000}" TableSize="TableSize.Compact">
    <FooterTemplate>
        <tr>
            @if (DataTableDynamicContext != null && DataTableDynamicContext.DataTable.Rows.Count > 0)
            {
                @foreach (DataColumn property in DataTableDynamicContext.DataTable.Columns)
                {
                    <td>
                        @*  //如果不是 代理商名称,部门 列 *@
                        @* @if (property.ColumnName != "代理商名称" && property.ColumnName != "部门" && property.ColumnName != "销售员" && Type.GetTypeCode(property.DataType) == TypeCode.String) *@
                        @if (Type.GetTypeCode(property.DataType) != TypeCode.String && property.ColumnName != "序号" && property.ColumnName != "差异率")
                        {
                            //当前列的合计
                            var sum = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull(property.ColumnName)) return 0m;
                                object value = dr[property.ColumnName];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            @sum
                        }
                        else if (property.ColumnName == "差异率")
                        {
                            //销售差异
                            var sum1 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull("差异")) return 0m;
                                object value = dr["差异"];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            //同期销售额
                            var sum2 = DataTableDynamicContext.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull("去年")) return 0m;
                                object value = dr["去年"];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            if (sum2 > 0)
                            {
                                @((sum1 / sum2).ToString("p2"))
                            }
                        }


                        @if (property.ColumnName == "序号")
                        {
                            @:合计
                        }

                    </td>
                }
            }

        </tr>
    </FooterTemplate>
</Table>

<Table TItem="DynamicObject" DynamicContext="DataTableDynamicContextDetail"
       IsBordered="true" IsMultipleSelect="false" ShowToolbar="true" ShowRefresh="false" ShowDefaultButtons="false" IsPagination="true" ShowExportButton="true"
       OnExportAsync="OnExportDetailAsync" OnSort="OnSortDetail" ShowFooter="true" IsStriped="true" Height="600" IsFixedHeader="true" PageItemsSource="new int[]{1000,2000,3000,4000,5000,10000}" TableSize="TableSize.Compact">
    <FooterTemplate>
        <tr>
            @if (DataTableDynamicContextDetail != null && DataTableDynamicContextDetail.DataTable.Rows.Count > 0)
            {
                @foreach (DataColumn property in DataTableDynamicContextDetail.DataTable.Columns)
                {
                    <td>
                        @*  //如果不是 代理商名称,部门 列 *@
                        @* @if (property.ColumnName != "代理商名称" && property.ColumnName != "部门" && property.ColumnName != "销售员" && Type.GetTypeCode(property.DataType) == TypeCode.String) *@
                        @if (Type.GetTypeCode(property.DataType) != TypeCode.String && property.ColumnName != "序号")
                        {
                            //当前列的合计
                            var sum = DataTableDynamicContextDetail.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull(property.ColumnName)) return 0m;
                                object value = dr[property.ColumnName];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            @sum
                        }
                        @if (property.ColumnName == "序号")
                        {
                            @:合计
                        }
                       

                    </td>
                }
            }

        </tr>
    </FooterTemplate>
</Table>

<Table TItem="DynamicObject" DynamicContext="DataTableDynamicContextDetail2"
       IsBordered="true" IsMultipleSelect="false" ShowToolbar="true" ShowRefresh="false" ShowDefaultButtons="false" IsPagination="true" ShowExportButton="true"
       OnExportAsync="OnExportDetail2Async" OnSort="OnSortDetail2" ShowFooter="true" IsStriped="true" Height="600" IsFixedHeader="true" PageItemsSource="new int[]{1000,2000,3000,4000,5000,10000}" TableSize="TableSize.Compact">
    <FooterTemplate>
        <tr>
            @if (DataTableDynamicContextDetail2 != null && DataTableDynamicContextDetail2.DataTable.Rows.Count > 0)
            {
                @foreach (DataColumn property in DataTableDynamicContextDetail2.DataTable.Columns)
                {
                    <td>
                        @*  //如果不是 代理商名称,部门 列 *@
                        @* @if (property.ColumnName != "代理商名称" && property.ColumnName != "部门" && property.ColumnName != "销售员" && Type.GetTypeCode(property.DataType) == TypeCode.String) *@
                        @if (Type.GetTypeCode(property.DataType) != TypeCode.String && property.ColumnName != "序号")
                        {
                            //当前列的合计
                            var sum = DataTableDynamicContextDetail2.DataTable.AsEnumerable().Sum(dr =>
                            {
                                if (dr.IsNull(property.ColumnName)) return 0m;
                                object value = dr[property.ColumnName];
                                if (value is int)
                                    return (decimal)(int)value;
                                else if (value is decimal)
                                    return (decimal)value;
                                else
                                    return 0m;
                            });
                            @sum
                        }
                        @if (property.ColumnName == "序号")
                        {
                            @:合计
                        }

                    </td>
                }
            }

        </tr>
    </FooterTemplate>
</Table>


@code {
    private string username;

    [Inject]
    [NotNull]
    private ITableExport? TableExport { get; set; }
    [Inject]
    [NotNull]
    private ToastService? Toast { get; set; }

    private List<View_Custom> View_CustomList = new List<View_Custom>();
    private List<View_Depart> 事业部List = new List<View_Depart>();
    //时间范围
    private DateTimeRangeValue NormalDateTimeRangeValue { get; set; } = new();

    private IEnumerable<SelectedItem> 客户列表Items => View_CustomList.Select(i => new SelectedItem(i.cCusCode!, i.cCusName!)).ToList();
    private IEnumerable<SelectedItem> 事业部Items => 事业部List.Select(i => new SelectedItem(i.cDepName!, i.cDepCode! + "--" + i.cDepName!)).ToList();

    private List<View_Person> View_CustomPersonList = new List<View_Person>();
    private IEnumerable<string> 选中业务员List { get; set; }
    private string 选中业务员InStr = "";
    private IEnumerable<SelectedItem> 业务员Items => View_CustomPersonList.Select(i => new SelectedItem(i.cPersonName!, i.cPersonName!)).ToList();
    private List<View_Custom> view_Customs = new List<View_Custom>();
    private SelectedItem? 选中养殖类别 { get; set; }
    private IEnumerable<SelectedItem> 养殖类别Items => view_Customs.Select(i => new SelectedItem(i.养殖类别!, i.养殖类别!)).ToList();
    [NotNull]
    private IEnumerable<SelectedItem>? 代理商类型 { get; set; } = new List<SelectedItem>(new List<SelectedItem>
    {
        new SelectedItem { Text = "直营", Value = "直营" },
        new SelectedItem { Text = "代理", Value = "代理"},
        new SelectedItem { Text = "加盟", Value = "加盟"}
    });

    [NotNull]
    private IEnumerable<SelectedItem>? 发货回款类别 { get; set; } = new List<SelectedItem>(new List<SelectedItem>
    {
        new SelectedItem { Text = "发货", Value = "发货" },
        new SelectedItem { Text = "回款", Value = "回款"}
    });

    [NotNull]
    private IEnumerable<SelectedItem>? 查询列 { get; set; } = new List<SelectedItem>(new List<SelectedItem>
    {
                new SelectedItem { Text = "事业部", Value = "事业部" },
                new SelectedItem { Text = "部门", Value = "部门"},
                new SelectedItem { Text = "客户", Value = "代理商名称" },
                new SelectedItem { Text = "分类", Value = "分类" },
                new SelectedItem { Text = "业务员", Value = "销售员" }
    });

    private string 选中查询列 { get; set; }


    private SelectedItem? 选中客户 { get; set; }
    private IEnumerable<string> 选中事业部List { get; set; }
    private string 选中门店InStr = "";
    private string 选中代理商类型 { get; set; }
    private string 选中发货回款类别 { get; set; }

    //表格数据
    [NotNull]
    private DataTableDynamicContext? DataTableDynamicContext { get; set; }
    private DataTableDynamicContext? DataTableDynamicContextDetail { get; set; }
    private DataTableDynamicContext? DataTableDynamicContextDetail2 { get; set; }

    protected override async void OnInitialized()
    {
        var authState = await authenticationStateProvider.GetAuthenticationStateAsync();
        var user = authState.User;
        username = user.Identity.Name;
        //今年1月1日
        NormalDateTimeRangeValue.Start = new DateTime(DateTime.Now.Year, 1, 1);
        //今天
        NormalDateTimeRangeValue.End = DateTime.Now.AddDays(-1);

        View_CustomList = _SqlSugarScope.Queryable<View_Custom>().ToList();
        //加一个请选择
        View_CustomList.Insert(0, new View_Custom { cCusCode = "", cCusName = "请选择" });
        List<View_Custom> cus = _SqlSugarScope.Queryable<View_Custom>().Where(it => !string.IsNullOrEmpty(it.养殖类别)).Distinct().Select(it => new View_Custom { 养殖类别 = it.养殖类别 }).ToList();
        view_Customs = cus;
        view_Customs.Insert(0, new View_Custom { 养殖类别 = "请选择" });
        view_Customs.Add(new View_Custom { 养殖类别 = "非小棚虾" });
        //读取配置文件
        // var PaiChuShiYeBu = _congifuration.GetSection("PaiChuShiYeBu").Value;
        // 事业部List = _SqlSugarScope.Queryable<View_Depart>().Where($"cDepCode not in ({PaiChuShiYeBu})").OrderBy(it => it.cDepCode).ToList();

        //读取配置文件
        UserDataAuth userDataAuth = _SqlSugarScope.Queryable<UserDataAuth>().Where(it => it.UserName == username).First();
        List<Guid> guids = userDataAuth.CanViewDepart.Split(",").ToList().Select(it => Guid.Parse(it)).ToList();
        List<Sys_Department> depts = _SqlSugarScope.Queryable<Sys_Department>().Where(it => guids.Contains(it.DepartmentId)).ToList();
        List<string> deptNames = depts.Select(it => it.DepartmentName).ToList();
        var PaiChuBuMen = _congifuration.GetSection("PaiChuBuMen").Value;
        List < View_Depart > deptss = _SqlSugarScope.Queryable<View_Depart>().Where($"cDepCode not in ({PaiChuBuMen})").ToList();
        事业部List = deptss.Where(it => deptNames.Contains(it.cDepName)).ToList();

        // //加一个请选择
        // View_DepartList.Insert(0, new View_Depart { cDepCode = "", cDepName = "请选择" });

        //读取业务员配置文件
        var PaiChuYeWuYuan = _congifuration.GetSection("PaiChuYeWuYuan").Value;
        // string sql = $"select distinct cPersonName,cPersonCode from View_Person where cPersonCode not in ({PaiChuYeWuYuan}) order by cPersonCode";
        string sql = $"select distinct cPersonName,cCusPPerson from View_CustomPerson where cCusPPerson not in ({PaiChuYeWuYuan})";

        View_CustomPersonList = _SqlSugarScope.Ado.SqlQuery<View_Person>(sql);

    }
    private Task OnNormalConfirm(DateTimeRangeValue value)
    {
        NormalLogger.LogDebug($"选择的时间范围是: {value.Start:yyyy-MM-dd HH:mm:ss} - {value.End:yyyy-MM-dd HH:mm:ss}");
        //遍历输出选中的客户
        if (选中客户 != null)
        {
            NormalLogger.LogDebug($"选中的客户是: {选中客户.Text}");
        }
        //遍历输出选中的部门
        if (选中事业部List != null)
        {
            foreach (var item in 选中事业部List)
            {

            }
        }
        return Task.CompletedTask;
    }
    private IEnumerable<SelectedItem> OnSearchTextChanged(string searchText)
    {
        return View_CustomList.Where(i => i.cCusName!.Contains(searchText, StringComparison.OrdinalIgnoreCase)).Select(i => new SelectedItem(i.cCusCode!, i.cCusName!));
    }

    private Task 部门选择改变事件(IEnumerable<SelectedItem> items)
    {
        选中门店InStr = string.Join("','", items.Select(it => it.Value).ToArray());
        return Task.CompletedTask;
    }
    private Task 业务员选择改变事件(IEnumerable<SelectedItem> items)
    {
        选中业务员InStr = string.Join("','", items.Select(it => it.Text).ToArray());
        return Task.CompletedTask;
    }

    private async Task<bool> OnExportAsync(ITableExportDataContext<DynamicObject> context)
    {
        // 导出符合条件的所有数据 data
        var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), context.Columns, $"原扩展分析表各业务经营月度报表_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 返回 true 时自动弹出提示框
        // await ShowToast(ret);
        return ret;
    }

    private async void Button_daochu(MouseEventArgs e)
    {
        // 导出符合条件的所有数据 data
        if (DataTableDynamicContext != null)
        {
            var ret = await TableExport.ExportExcelAsync(DataTableDynamicContext.GetItems(), DataTableDynamicContext.GetColumns(), $"业务经营月度报表_{DateTime.Now:yyyyMMddHHmmss}.xlsx");
            ToastService.Success("提示", "导出成功");
        }

    }

    private string OnSort(string sortName, SortOrder sortOrder)
    {

        string sortString = "";
        if (sortOrder == SortOrder.Asc)
        {
            sortString = $"{sortName} asc";
        }
        else if (sortOrder == SortOrder.Desc)
        {
            sortString = $"{sortName} desc";
        }
        else
        {
            sortString = $"{sortName}";
        }
        DataTable sortTable = DataTableDynamicContext.DataTable.Clone();

        DataView dv = DataTableDynamicContext.DataTable.DefaultView;
        dv.Sort = sortString;
        sortTable = dv.ToTable();
        for (int i = 0; i < sortTable.Rows.Count; i++)
        {
            sortTable.Rows[i]["序号"] = i + 1;
        }
        DataTableDynamicContext = new DataTableDynamicContext(sortTable, (context, col) =>

            {
                //所有列可排序
                col.Sortable = true;
                col.DefaultSort = true;
                if (col.GetFieldName() == "差异率")
                {
                    col.Width = 80;
                    col.FormatString = "P2";
                }
                else if (col.GetFieldName() == "序号")
                {
                    col.Width = 40;
                }
                else if (col.GetFieldName() == "客户名称")
                {
                    col.Width = 120;
                    // col.TextEllipsis = true;
                }
                else if (col.GetFieldName() == "分类")
                {
                    col.Width = 40;
                }
                else
                {
                    col.Width = 80;
                }
            });
        StateHasChanged();
        return sortString;
    }

    private async Task<bool> OnExportDetailAsync(ITableExportDataContext<DynamicObject> context)
    {
        // 导出符合条件的所有数据 data
        var ret = await TableExport.ExportExcelAsync(DataTableDynamicContextDetail.GetItems(), context.Columns, $"原06代理业务数据穿透分析_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 返回 true 时自动弹出提示框
        // await ShowToast(ret);
        return ret;
    }

    private string OnSortDetail(string sortName, SortOrder sortOrder)
    {

        string sortString = "";
        if (sortOrder == SortOrder.Asc)
        {
            sortString = $"{sortName} asc";
        }
        else if (sortOrder == SortOrder.Desc)
        {
            sortString = $"{sortName} desc";
        }
        else
        {
            sortString = $"{sortName}";
        }
        DataTable sortTable = DataTableDynamicContextDetail.DataTable.Clone();

        DataView dv = DataTableDynamicContextDetail.DataTable.DefaultView;
        dv.Sort = sortString;
        sortTable = dv.ToTable();
        for (int i = 0; i < sortTable.Rows.Count; i++)
        {
            sortTable.Rows[i]["序号"] = i + 1;
        }
        DataTableDynamicContextDetail = new DataTableDynamicContext(sortTable, (context, col) =>

            {
                //所有列可排序
                col.Sortable = true;
                col.DefaultSort = true;
                if (col.GetFieldName() == "差异率")
                {
                    col.FormatString = "P2";
                }
                else if (col.GetFieldName() == "序号")
                {
                    col.Width = 40;
                }
                else if (col.GetFieldName() == "客户名称")
                {
                    col.Width = 120;
                    // col.TextEllipsis = true;
                }
                else if (col.GetFieldName() == "分类")
                {
                    col.Width = 40;
                }
                else
                {
                    col.Width = 80;
                }
            });
        StateHasChanged();
        return sortString;
    }

    private async Task<bool> OnExportDetail2Async(ITableExportDataContext<DynamicObject> context)
    {
        // 导出符合条件的所有数据 data
        var ret = await TableExport.ExportExcelAsync(DataTableDynamicContextDetail2.GetItems(), context.Columns, $"原06代理业务数据穿透分析_{DateTime.Now:yyyyMMddHHmmss}.xlsx");

        // 返回 true 时自动弹出提示框
        // await ShowToast(ret);
        return ret;
    }

    private string OnSortDetail2(string sortName, SortOrder sortOrder)
    {

        string sortString = "";
        if (sortOrder == SortOrder.Asc)
        {
            sortString = $"{sortName} asc";
        }
        else if (sortOrder == SortOrder.Desc)
        {
            sortString = $"{sortName} desc";
        }
        else
        {
            sortString = $"{sortName}";
        }
        DataTable sortTable = DataTableDynamicContextDetail2.DataTable.Clone();

        DataView dv = DataTableDynamicContextDetail2.DataTable.DefaultView;
        dv.Sort = sortString;
        sortTable = dv.ToTable();
        for (int i = 0; i < sortTable.Rows.Count; i++)
        {
            sortTable.Rows[i]["序号"] = i + 1;
        }
        DataTableDynamicContextDetail2 = new DataTableDynamicContext(sortTable, (context, col) =>

            {
                //所有列可排序
                col.Sortable = true;
                col.DefaultSort = true;
                if (col.GetFieldName() == "差异率")
                {
                    col.FormatString = "P2";
                }
                else if (col.GetFieldName() == "序号")
                {
                    col.Width = 40;
                }
                else if (col.GetFieldName() == "客户名称")
                {
                    col.Width = 120;
                    // col.TextEllipsis = true;
                }
                else if (col.GetFieldName() == "分类")
                {
                    col.Width = 40;
                }
                else
                {
                    col.Width = 80;
                }
            });
        StateHasChanged();
        return sortString;
    }



    private decimal sum1;
    private decimal sum2;
    private decimal sum3;
    private decimal sum4;
    private decimal sum5;
    private decimal sum6;
    private decimal sum7;
    private decimal sum8;

    private async void Button_chaxun_Click(MouseEventArgs e)
    {
        if (选中发货回款类别 == "发货")
        {
            发货汇总();
        }
        else
        {
            收款汇总();
        }
    }
    private async void 收款汇总()
    {
        //客户发货排名
        string sql;
        string 年度 = this.NormalDateTimeRangeValue.End.Year.ToString();
        string 今年开始日期 = this.NormalDateTimeRangeValue.Start.ToString("yyyy-MM-dd");
        string 今年结束日期 = this.NormalDateTimeRangeValue.End.ToString("yyyy-MM-dd");
        int 今年结束月度 = this.NormalDateTimeRangeValue.End.Month;

        string 去年开始日期 = this.NormalDateTimeRangeValue.Start.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年结束日期 = this.NormalDateTimeRangeValue.End.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年 = 去年开始日期.Substring(0, 4);
        string 今年sql;
        string 去年sql;

        if (string.IsNullOrEmpty(选中门店InStr) == true)
        {
            选中门店InStr += string.Join("','", 事业部List.Select(it => it.cDepName));
        }
        string 事业部清单 = "('" + 选中门店InStr + "')";

        string guid = Guid.NewGuid().ToString();
        // string loginuser = username;
        string loginuser = "admin";
        sql = "delete T扩展分析各业务经营月度 where usercode='" + loginuser + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        #region 收款数据-今年
        //塞入今年收款
        if (今年结束月度 >= 1)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,  " +
                "年度,一月回款)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cDwCode ,cPerson, ";
            sql += " '" + 年度 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 年度 + "' and month( convert(datetime, [dDate]))=1 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

        }
        if (今年结束月度 >= 2)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,   " +
                "年度,二月回款)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cDwCode ,cPerson, ";
            sql += " '" + 年度 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=2 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 3)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,   " +
                "年度,三月回款)";
            sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
            sql += " '" + 年度 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=3 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 4)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
                "年度,四月回款)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cDwCode ,cPerson,  ";
            sql += " '" + 年度 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=4";
            sql +=  " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 5)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
                "年度,五月回款)";
            sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
            sql += " '" + 年度 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=5 ";
            sql +=  " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 6)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
                "年度,六月回款)";
            sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
            sql += " '" + 年度 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=6 ";
            sql +=  " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 7)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
                "年度,七月回款)";
            sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
            sql += " '" + 年度 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=7 ";
            sql +=  " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 8)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
                "年度,八月回款)";
            sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
            sql += " '" + 年度 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=8 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 9)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
                "年度,九月回款)";
            sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
            sql += " '" + 年度 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=9 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 10)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
                "年度,十月回款)";
            sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
            sql += " '" + 年度 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=10 ";
            sql +=  " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 11)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
                "年度,十一月回款)";
            sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
            sql += " '" + 年度 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=11 ";
            sql +=  " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 12)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
                "年度,十二月回款)";
            sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
            sql += " '" + 年度 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=12 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        #endregion
        #region 收款数据-去年
        //塞往年收款
        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
                "年度,一月回款)";
        sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
        sql += " '" + 去年 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 去年 + "' and month( convert(datetime, [dDate]))=1 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);


        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
            "年度,二月回款)";
        sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
        sql += " '" + 去年 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=2 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
            "年度,三月回款)";
        sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
        sql += " '" + 去年 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=3 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
            "年度,四月回款)";
        sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
        sql += " '" + 去年 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=4";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
            "年度,五月回款)";
        sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
        sql += " '" + 去年 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=5 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
            "年度,六月回款)";
        sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
        sql += " '" + 去年 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=6 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
            "年度,七月回款)";
        sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
        sql += " '" + 去年 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=7 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
            "年度,八月回款)";
        sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
        sql += " '" + 去年 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=8 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
            "年度,九月回款)";
        sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
        sql += " '" + 去年 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=9 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
            "年度,十月回款)";
        sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
        sql += " '" + 去年 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=10 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
            "年度,十一月回款)";
        sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
        sql += " '" + 去年 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=11 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode,   cPersonCode,    " +
            "年度,十二月回款)";
        sql += "select '" + guid + "','" + loginuser + "',  dDate,cDwCode ,cPerson, ";
        sql += " '" + 去年 + "',iRAmt from View_收款明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=12 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        #endregion
        #region 更新其他数据
        //更新客户
        sql = "update  a set a.代理商名称=b.cCusName,a.cDCCode=b.cDCCode ";
        sql += " from T扩展分析各业务经营月度 a, View_Custom b where a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        //更新分类
        sql = "update  a set a.分类=b.[cDCName] ";//地区分类
        sql += " from T扩展分析各业务经营月度 a, View_DistrictClass b where a.[cDCCode]=b.[cDCCode] and a.guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        //更新销售员
        sql = "update  a set a.销售员=b.cPersonName ";
        sql += " from T扩展分析各业务经营月度 a, View_CustomPerson b where a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        // sql = "update  a set a.销售员=b.cPersonName ";
        // sql += " from T扩展分析各业务经营月度 a, View_Person b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        //更新部门
        sql = "update  a set a.部门=b.DepartmentName,a.[Depart]=b.[cDepCode]  ";
        sql += " from T扩展分析各业务经营月度 a, View_PersonDepart b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        //更新事业部（就是一级部门）
        sql = "update  a set a.事业部=b.事业部 ";
        sql += " from T扩展分析各业务经营月度 a, T事业部 b where  substring(a.[Depart] ,0,4)=b.编号 and a.guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

         //养殖类别
            sql = "update  a set a.养殖类别=b.养殖类别 ";
        sql += " from T扩展分析各业务经营月度 a, View_Custom b where  a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

        #endregion
        #region 汇总
        //汇总处理
        //当年
        sql = " delete T扩展分析各业务经营月度汇总 where guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        sql = " INSERT into  T扩展分析各业务经营月度汇总(  guid, 事业部, 部门, 分类,代理商名称, 销售员, 年类型,";
        sql += " 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月,十二月)";
        sql += " SELECT  '" + guid + "', 事业部, 部门,分类,代理商名称,  销售员,'今年', ";
        sql += " sum(isnull(一月回款,0)),  sum(isnull(二月回款,0)),  sum(isnull( 三月回款,0)), sum(isnull(四月回款,0)),  sum(isnull(五月回款,0)),  sum(isnull(六月回款,0)),";
        sql += " sum(isnull(七月回款,0)),  sum(isnull(八月回款,0)),  sum(isnull(九月回款,0)),  sum(isnull(十月回款,0)),  sum(isnull(十一月回款,0)),sum(isnull(十二月回款 ,0)) ";
        sql += " FROM T扩展分析各业务经营月度 where guid='" + guid + "' and 年度='" + 年度 + "' ";
        if (事业部清单 != "('')")
        {
            //曹经理要求部门选择到下级部门，所以筛选条件要改为部门，不用事业部
            // sql += " and 事业部 in " + 事业部清单;
            sql += " and 部门 in " + 事业部清单;
        }
        if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
        {
            sql += " and 代理商名称 like '%" + this.选中客户?.Text + "%'";
        }

        if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text != "非小棚虾")
        {
            sql += " and 养殖类别='" + this.选中养殖类别.Text + "'";
        }
        if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text == "非小棚虾")
        {
            sql += " and 养殖类别 is null";
        }

        sql += " and 分类='" + 选中代理商类型 + "'";
        sql += "group by 事业部, 部门,分类,代理商名称,  销售员 ";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        sql = " update  T扩展分析各业务经营月度汇总 set 本年=[一月]+ [二月]+[三月]+[四月]+[五月]+[六月]+[七月]+[八月]+[九月]+ [十月]+ [十一月]+[十二月]";
        sql += " where guid='" + guid + "' and 年类型='今年' ";
        _SqlSugarScope.Ado.ExecuteCommand(sql);


        //去年

        _SqlSugarScope.Ado.ExecuteCommand(sql);
        sql = " INSERT into  T扩展分析各业务经营月度汇总(  guid, 事业部, 部门, 分类,代理商名称, 销售员, 年类型,";
        sql += " 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月,十二月)";
        sql += " SELECT  '" + guid + "', 事业部, 部门,分类,代理商名称,  销售员,'去年', ";
        sql += " sum(isnull(一月回款,0)),  sum(isnull(二月回款,0)),  sum(isnull( 三月回款,0)), sum(isnull(四月回款,0)),  sum(isnull(五月回款,0)),  sum(isnull(六月回款,0)),";
        sql += " sum(isnull(七月回款,0)),  sum(isnull(八月回款,0)),  sum(isnull(九月回款,0)),  sum(isnull(十月回款,0)),  sum(isnull(十一月回款,0)),sum(isnull(十二月回款 ,0)) ";
        sql += " FROM T扩展分析各业务经营月度 where guid='" + guid + "' and 年度='" + 去年 + "' ";
        if (事业部清单 != "('')")
        {
            //曹经理要求部门选择到下级部门，所以筛选条件要改为部门，不用事业部
            // sql += " and 事业部 in " + 事业部清单;
            sql += " and 部门 in " + 事业部清单;
        }
        if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
        {
            sql += " and 代理商名称 like '%" + this.选中客户?.Text + "%'";
        }
        if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text != "非小棚虾")
        {
            sql += " and 养殖类别='" + this.选中养殖类别.Text + "'";
        }
        if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text == "非小棚虾")
        {
            sql += " and 养殖类别 is null";
        }
        sql += " and 分类='" + 选中代理商类型 + "'";
        sql += "group by 事业部, 部门,分类,代理商名称,  销售员 ";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "update T扩展分析各业务经营月度汇总 set 去年= 0 ,差异 = 0,差异率= 0 where guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = " update  T扩展分析各业务经营月度汇总 set 去年=[一月]+ [二月]+[三月]+[四月]+[五月]+[六月]+[七月]+[八月]+[九月]+ [十月]+ [十一月]+[十二月]";
        sql += " where guid='" + guid + "' and 年类型='去年' ";
        _SqlSugarScope.Ado.ExecuteCommand(sql);


        sql = @"SELECT  事业部, 部门, 分类,代理商名称, 销售员, 年类型, Convert(decimal(18,4),一月/10000) 一月, Convert(decimal(18,4),二月/10000) 二月, Convert(decimal(18,4),三月/10000) 三月, Convert(decimal(18,4),四月/10000) 四月, Convert(decimal(18,4),五月/10000) 五月, Convert(decimal(18,4),六月/10000) 六月, Convert(decimal(18,4),七月/10000) 七月,
        Convert(decimal(18,4),八月/10000) 八月, Convert(decimal(18,4),九月/10000) 九月, Convert(decimal(18,4),十月/10000) 十月, Convert(decimal(18,4),十一月/10000) 十一月,
                Convert(decimal(18,4),十二月/10000) 十二月
FROM T扩展分析各业务经营月度汇总 where guid='" + guid + "' and 年类型='去年'";
        sql += @"union all
        SELECT 事业部, 部门, 分类,代理商名称, 销售员,'去年' 年类型, Convert(decimal(18,4),0/10000) 一月, Convert(decimal(18,4),0/10000) 二月, Convert(decimal(18,4),0/10000) 三月, Convert(decimal(18,4),0/10000) 四月, Convert(decimal(18,4),0/10000) 五月, Convert(decimal(18,4),0/10000) 六月, Convert(decimal(18,4),0/10000) 七月,
        Convert(decimal(18,4),0/10000) 八月, Convert(decimal(18,4),0/10000) 九月, Convert(decimal(18,4),0/10000) 十月, Convert(decimal(18,4),0/10000) 十一月,
                Convert(decimal(18,4),0/10000) 十二月
                FROM T扩展分析各业务经营月度汇总 AS a
                WHERE 年类型 = '今年'
                AND NOT EXISTS (
                    SELECT 1
                    FROM T扩展分析各业务经营月度汇总 AS b
                    WHERE b.年类型 = '去年'
                    AND b.事业部 = a.事业部
                    AND b.部门 = a.部门
                    AND b.代理商名称 = a.代理商名称
                    AND b.销售员 = a.销售员 and guid='" + guid + "') and guid='" + guid + "'";
        DataTable dataTable去年 = _SqlSugarScope.Ado.GetDataTable(sql);

        // this.GridView2.DataSource = dataTable去年;
        // ViewState["dataTable去年"] = dataTable去年;
        // this.GridView2.DataBind();
        // DataTableDynamicContextDetail = new DataTableDynamicContext(dataTable去年, (context, col) =>
        // {
        //     //所有列可排序
        //     col.Sortable = true;
        //     col.DefaultSort = true;
        // });
        去年筛选业务员(dataTable去年);

        sql = " update a set a.去年=b.去年金额 ";
        sql += " from T扩展分析各业务经营月度汇总 a ," +
            " (select 事业部, 部门,分类,代理商名称,  销售员,sum(isnull(去年,0)) 去年金额  ";
        sql += " FROM T扩展分析各业务经营月度汇总 where guid='" + guid + "' and 年类型='去年'  group by  事业部, 部门,分类,代理商名称,  销售员 ) b";
        sql += " where a.guid='" + guid + "' and a.年类型='今年' and a.事业部=b.事业部 and a.部门=b.部门 ";
        sql += " and a.分类=b.分类 and a.代理商名称 =b.代理商名称 and a.销售员=b.销售员";

        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "update T扩展分析各业务经营月度汇总 set 差异=[本年]-[去年] where guid='" + guid + "' and 年类型='今年'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "update T扩展分析各业务经营月度汇总 set 差异率=Convert(decimal(18,2),[差异]/[本年]*100,2) where guid='" + guid + "' and 年类型='今年' and 本年 > 0";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = @"SELECT  事业部, 部门, 分类,代理商名称, 销售员, 年类型, Convert(decimal(18,4),一月/10000) 一月, Convert(decimal(18,4),二月/10000) 二月, Convert(decimal(18,4),三月/10000) 三月, Convert(decimal(18,4),四月/10000) 四月, Convert(decimal(18,4),五月/10000) 五月, Convert(decimal(18,4),六月/10000) 六月, Convert(decimal(18,4),七月/10000) 七月,
        Convert(decimal(18,4),八月/10000) 八月, Convert(decimal(18,4),九月/10000) 九月, Convert(decimal(18,4),十月/10000) 十月, Convert(decimal(18,4),十一月/10000) 十一月,
                Convert(decimal(18,4),十二月/10000) 十二月, Convert(decimal(18,4),本年/10000) 本年, Convert(decimal(18,4),去年/10000) 去年, Convert(decimal(18,4),差异/10000) 差异, 差异率
FROM T扩展分析各业务经营月度汇总 where guid='" + guid + "' and 年类型='今年'";

        sql += @"union all 
                 SELECT 事业部, 部门, 分类,代理商名称, 销售员,'今年' 年类型, Convert(decimal(18,4),0/10000) 一月, Convert(decimal(18,4),0/10000) 二月, Convert(decimal(18,4),0/10000) 三月, Convert(decimal(18,4),0/10000) 四月, Convert(decimal(18,4),0/10000) 五月, Convert(decimal(18,4),0/10000) 六月, Convert(decimal(18,4),0/10000) 七月,
        Convert(decimal(18,4),0/10000) 八月, Convert(decimal(18,4),0/10000) 九月, Convert(decimal(18,4),0/10000) 十月, Convert(decimal(18,4),0/10000) 十一月,
                Convert(decimal(18,4),0/10000) 十二月, Convert(decimal(18,4),isnull(本年,0)/10000) 本年,Convert(decimal(18,4),去年/10000) 去年,Convert(decimal(18,4),(isnull(本年,0) - 去年)/10000) 差异, (isnull(本年,0) - 去年)/[去年] 差异率
                FROM T扩展分析各业务经营月度汇总 AS a
                WHERE 年类型 = '去年'
                AND NOT EXISTS (
                    SELECT 1
                    FROM T扩展分析各业务经营月度汇总 AS b
                    WHERE b.年类型 = '今年'
                    AND b.事业部 = a.事业部
                    AND b.部门 = a.部门
                    AND b.代理商名称 = a.代理商名称
                    AND b.销售员 = a.销售员 and guid='" + guid + "') and guid='" + guid + "'";

        DataTable dataTable今年 = _SqlSugarScope.Ado.GetDataTable(sql);

        // this.GridView1.DataSource = dataTable今年;
        // ViewState["dataTable今年"] = dataTable今年;
        // this.GridView1.DataBind();
        // DataTableDynamicContext = new DataTableDynamicContext(dataTable今年, (context, col) =>
        // {
        //     //所有列可排序
        //     col.Sortable = true;
        //     col.DefaultSort = true;
        // });
        今年筛选业务员(dataTable今年);
        ///差异

        sql = "select 事业部, 部门, 分类,'差异' 年类型, 代理商名称, 销售员,Convert(decimal(18,4),sum(一月)/10000,4) 一月,Convert(decimal(18,4),sum(二月)/10000,4) 二月,Convert(decimal(18,4),sum(三月)/10000,4) 三月," +
            "Convert(decimal(18,4),sum(四月)/10000,4) 四月,Convert(decimal(18,4),sum(五月)/10000,4) 五月,Convert(decimal(18,4),sum(六月)/10000,4) 六月,Convert(decimal(18,4),sum(七月)/10000,4) 七月,Convert(decimal(18,4),sum(八月)/10000,4) 八月,Convert(decimal(18,4),sum(九月)/10000,4) 九月," +
            "Convert(decimal(18,4),sum(十月)/10000,4) 十月,Convert(decimal(18,4),sum(十一月)/10000,4) 十一月,Convert(decimal(18,4),sum(十二月)/10000,4) 十二月 from ";
        sql += "( SELECT     事业部, 部门, 分类, 代理商名称, 销售员, 年类型, 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月, 十二月";
        sql += " FROM      T扩展分析各业务经营月度汇总 WHERE guid='" + guid + "' and  (年类型 = '今年') ";
        sql += " UNION ALL ";
        sql += " SELECT     事业部, 部门, 分类, 代理商名称, 销售员, 年类型, 一月 * - 1 AS Expr1, 二月 * - 1 AS Expr2, 三月 * - 1 AS Expr3,四月 * - 1 AS Expr4, ";
        sql += " 五月 * - 1 AS Expr5, 六月 * - 1 AS Expr6, 七月 * - 1 AS Expr7, 八月 * - 1 AS Expr8, 九月 * - 1 AS Expr9, ";
        sql += " 十月 * - 1 AS Expr10, 十一月 * - 1 AS Expr11, 十二月 * - 1 AS Expr12";
        sql += " FROM      T扩展分析各业务经营月度汇总  WHERE guid='" + guid + "' and (年类型 = '去年')) t " +
            " group by 事业部, 部门, 分类, 代理商名称, 销售员";
        DataTable dataTable差异 = _SqlSugarScope.Ado.GetDataTable(sql);

        // this.GridView3.DataSource = dataTable差异;
        // ViewState["dataTable差异"] = dataTable差异;
        // this.GridView3.DataBind();
        // DataTableDynamicContextDetail2 = new DataTableDynamicContext(dataTable差异, (context, col) =>
        // {
        //     //所有列可排序
        //     col.Sortable = true;
        //     col.DefaultSort = true;
        // });
        差异筛选业务员(dataTable差异);
        #endregion
        await ToastService.Information("提示", "查询成功");
    }

    private async void 发货汇总()
    {
        //客户发货排名

        string sql;
        string 年度 = this.NormalDateTimeRangeValue.End.Year.ToString();
        string 今年开始日期 = this.NormalDateTimeRangeValue.Start.ToString("yyyy-MM-dd");
        string 今年结束日期 = this.NormalDateTimeRangeValue.End.ToString("yyyy-MM-dd");
        int 今年结束月度 = this.NormalDateTimeRangeValue.End.Month;

        string 去年开始日期 = this.NormalDateTimeRangeValue.Start.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年结束日期 = this.NormalDateTimeRangeValue.End.AddYears(-1).ToString("yyyy-MM-dd");
        string 去年 = 去年开始日期.Substring(0, 4);
        string 今年sql;
        string 去年sql;

        if (string.IsNullOrEmpty(选中门店InStr) == true)
        {
            选中门店InStr += string.Join("','", 事业部List.Select(it => it.cDepName));
        }
        string 事业部清单 = "('" + 选中门店InStr + "')";

        string guid = Guid.NewGuid().ToString();
        string loginuser = username;
        sql = "delete T扩展分析各业务经营月度 where usercode='" + loginuser + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        #region 发货数据-今年
        //塞入今年发货
        if (今年结束月度 >= 1)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,  " +
                "年度,一月发货)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
            sql += " '" + 年度 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 年度 + "' and month( convert(datetime, [dDate]))=1 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);

        }
        if (今年结束月度 >= 2)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,   " +
                "年度,二月发货)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
            sql += " '" + 年度 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=2 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";

            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 3)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode, cwhcode,    " +
                "年度,三月发货)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
            sql += " '" + 年度 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=3 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";

            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 4)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,  cwhcode,   " +
                "年度,四月发货)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode, cwhcode, ";
            sql += " '" + 年度 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=4";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";

            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 5)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
                "年度,五月发货)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
            sql += " '" + 年度 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=5 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 6)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
                "年度,六月发货)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
            sql += " '" + 年度 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=6 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 7)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
                "年度,七月发货)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
            sql += " '" + 年度 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=7 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 8)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
                "年度,八月发货)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
            sql += " '" + 年度 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=8 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 9)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
                "年度,九月发货)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
            sql += " '" + 年度 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=9 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 10)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
                "年度,十月发货)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
            sql += " '" + 年度 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=10 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 11)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
                "年度,十一月发货)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
            sql += " '" + 年度 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=11 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        if (今年结束月度 >= 12)
        {
            sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
                "年度,十二月发货)";
            sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
            sql += " '" + 年度 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 年度 + "'" +
                " and month( convert(datetime, [dDate]))=12 ";
            sql += " and dDate<='" + 今年结束日期 + "'and dDate>='" + 今年开始日期 + "'";
            _SqlSugarScope.Ado.ExecuteCommand(sql);
        }
        #endregion
        #region 发货数据-去年
        //塞往年发货
        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
                "年度,一月发货)";
        sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
        sql += " '" + 去年 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 去年 + "' and month( convert(datetime, [dDate]))=1 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);


        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
            "年度,二月发货)";
        sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
        sql += " '" + 去年 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=2 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
            "年度,三月发货)";
        sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
        sql += " '" + 去年 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=3 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
            "年度,四月发货)";
        sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
        sql += " '" + 去年 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=4";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
            "年度,五月发货)";
        sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
        sql += " '" + 去年 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=5 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
            "年度,六月发货)";
        sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
        sql += " '" + 去年 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=6 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
            "年度,七月发货)";
        sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
        sql += " '" + 去年 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=7 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
            "年度,八月发货)";
        sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
        sql += " '" + 去年 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=8 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
            "年度,九月发货)";
        sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
        sql += " '" + 去年 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=9 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
            "年度,十月发货)";
        sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
        sql += " '" + 去年 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=10 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
            "年度,十一月发货)";
        sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
        sql += " '" + 去年 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=11 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "insert into T扩展分析各业务经营月度( guid, usercode, dDate,  cCusCode, cDLCode, irowno, cPersonCode,cwhcode,    " +
            "年度,十二月发货)";
        sql += "select '" + guid + "','" + loginuser + "', dDate,cCusCode ,cDLCode,irowno,cPersonCode,cwhcode, ";
        sql += " '" + 去年 + "',iMoney from View_发票明细 where 1=1  and [period]='" + 去年 + "'" +
            " and month( convert(datetime, [dDate]))=12 ";
        sql += "and dDate<='" + 去年结束日期 + "'and dDate>='" + 去年开始日期 + "'";

        _SqlSugarScope.Ado.ExecuteCommand(sql);
        #endregion
        #region 更新其他数据

        //更新客户
        sql = "update  a set a.代理商名称=b.cCusName,a.cDCCode=b.cDCCode ";
        sql += " from T扩展分析各业务经营月度 a, View_Custom b where a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        //更新客户


        //更新分类
        sql = "update  a set a.分类=b.[cDCName] ";//地区分类
        sql += " from T扩展分析各业务经营月度 a, View_DistrictClass b where a.[cDCCode]=b.[cDCCode] and a.guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        // //更新销售员
        sql = "update  a set a.销售员=b.cPersonName ";
        sql += " from T扩展分析各业务经营月度 a, View_CustomPerson b where a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        // sql = "update  a set a.销售员=b.cPersonName ";
        // sql += " from T扩展分析各业务经营月度 a, View_Person b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);


        //更新部门
        sql = "update  a set a.部门=b.DepartmentName,a.[Depart]=b.[cDepCode]  ";
        sql += " from T扩展分析各业务经营月度 a, View_PersonDepart b where a.cPersonCode=b.cPersonCode and a.guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        //更新事业部（就是一级部门）
        sql = "update  a set a.事业部=b.事业部 ";
        sql += " from T扩展分析各业务经营月度 a, T事业部 b where  substring(a.[Depart] ,0,4)=b.编号 and a.guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        //养殖类别
        sql = "update  a set a.养殖类别=b.养殖类别 ";
        sql += " from T扩展分析各业务经营月度 a, View_Custom b where  a.cCusCode=b.cCusCode and a.guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        // //6	退货库单独处理金额
        // sql = "update T扩展分析各业务经营月度  set 一月发货=abs(一月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        // sql = "update T扩展分析各业务经营月度  set 二月发货=abs(二月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        // sql = "update T扩展分析各业务经营月度  set 三月发货=abs(三月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        // sql = "update T扩展分析各业务经营月度  set 四月发货=abs(四月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        // sql = "update T扩展分析各业务经营月度  set 五月发货=abs(五月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        // sql = "update T扩展分析各业务经营月度  set 六月发货=abs(六月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        // sql = "update T扩展分析各业务经营月度  set 七月发货=abs(七月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        // sql = "update T扩展分析各业务经营月度  set 八月发货=abs(八月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        // sql = "update T扩展分析各业务经营月度  set 九月发货=abs(九月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        // sql = "update T扩展分析各业务经营月度  set 十月发货=abs(十月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        // sql = "update T扩展分析各业务经营月度  set 十一月发货=abs(十一月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        // sql = "update T扩展分析各业务经营月度  set 十二月发货=abs(十二月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        // sql = "update T扩展分析各业务经营月度  set 二月发货=abs(二月发货) where cwhcode='06' and guid='" + guid + "' ";
        // _SqlSugarScope.Ado.ExecuteCommand(sql);
        #endregion
        #region 汇总
        //汇总处理
        //当年
        sql = " delete T扩展分析各业务经营月度汇总 where guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        sql = " INSERT into  T扩展分析各业务经营月度汇总(  guid, 事业部, 部门, 分类,代理商名称, 销售员, 年类型,";
        sql += " 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月,十二月)";
        sql += " SELECT  '" + guid + "', 事业部, 部门,分类,代理商名称,  销售员,'今年', ";
        sql += " sum(isnull(一月发货,0)),  sum(isnull(二月发货,0)),  sum(isnull( 三月发货,0)), sum(isnull(四月发货,0)),  sum(isnull(五月发货,0)),  sum(isnull(六月发货,0)),";
        sql += " sum(isnull(七月发货,0)),  sum(isnull(八月发货,0)),  sum(isnull(九月发货,0)),  sum(isnull(十月发货,0)),  sum(isnull(十一月发货,0)),sum(isnull(十二月发货 ,0)) ";
        sql += " FROM T扩展分析各业务经营月度 where guid='" + guid + "' and 年度='" + 年度 + "' ";
        if (事业部清单 != "('')")
        {
            //曹经理要求部门选择到下级部门，所以筛选条件要改为部门，不用事业部
            // sql += " and 事业部 in " + 事业部清单;
            sql += " and 部门 in " + 事业部清单;
        }
        if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
        {
            sql += " and 代理商名称 like '%" + this.选中客户?.Text + "%'";
        }
        sql += " and 分类='" + 选中代理商类型 + "'";
        if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text != "非小棚虾")
        {
            sql += " and 养殖类别='" + this.选中养殖类别.Text + "'";
        }
        if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text == "非小棚虾")
        {
            sql += " and 养殖类别 is null ";
        }
        sql += "group by 事业部, 部门,分类,代理商名称,  销售员 ";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "update T扩展分析各业务经营月度汇总 set 去年= 0 ,差异 = 0,差异率= 0 where guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = " update  T扩展分析各业务经营月度汇总 set 本年=[一月]+ [二月]+[三月]+[四月]+[五月]+[六月]+[七月]+[八月]+[九月]+ [十月]+ [十一月]+[十二月]";
        sql += " where guid='" + guid + "' and 年类型='今年' ";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        //去年
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        sql = " INSERT into  T扩展分析各业务经营月度汇总(  guid, 事业部, 部门, 分类,代理商名称, 销售员, 年类型,";
        sql += " 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月,十二月)";
        sql += " SELECT  '" + guid + "', 事业部, 部门,分类,代理商名称,  销售员,'去年', ";
        sql += " sum(isnull(一月发货,0)),  sum(isnull(二月发货,0)),  sum(isnull( 三月发货,0)), sum(isnull(四月发货,0)),  sum(isnull(五月发货,0)),  sum(isnull(六月发货,0)),";
        sql += " sum(isnull(七月发货,0)),  sum(isnull(八月发货,0)),  sum(isnull(九月发货,0)),  sum(isnull(十月发货,0)),  sum(isnull(十一月发货,0)),sum(isnull(十二月发货 ,0)) ";
        sql += " FROM T扩展分析各业务经营月度 where guid='" + guid + "' and 年度='" + 去年 + "' ";
        if (事业部清单 != "('')")
        {
            //曹经理要求部门选择到下级部门，所以筛选条件要改为部门，不用事业部
            // sql += " and 事业部 in " + 事业部清单;
            sql += " and 部门 in " + 事业部清单;
        }
        if (string.IsNullOrEmpty(this.选中客户?.Text) == false && this.选中客户?.Text != "请选择")
        {
            sql += " and 代理商名称 like '%" + this.选中客户?.Text + "%'";
        }
        sql += " and 分类='" + 选中代理商类型 + "'";

         if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text != "非小棚虾")
        {
            sql += " and 养殖类别='" + this.选中养殖类别.Text + "'";
        }
        if (this.选中养殖类别?.Text != "请选择" && this.选中养殖类别?.Text == "非小棚虾")
        {
            sql += " and 养殖类别 is null ";
        }

        sql += "group by 事业部, 部门,分类,代理商名称,  销售员 ";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        sql = " update  T扩展分析各业务经营月度汇总 set 去年=[一月]+ [二月]+[三月]+[四月]+[五月]+[六月]+[七月]+[八月]+[九月]+ [十月]+ [十一月]+[十二月]";
        sql += " where guid='" + guid + "' and 年类型='去年' ";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        sql = @"SELECT  事业部, 部门, 分类,代理商名称, 销售员, 年类型, Convert(decimal(18,4),一月/10000) 一月, Convert(decimal(18,4),二月/10000) 二月, Convert(decimal(18,4),三月/10000) 三月, Convert(decimal(18,4),四月/10000) 四月, Convert(decimal(18,4),五月/10000) 五月, Convert(decimal(18,4),六月/10000) 六月, Convert(decimal(18,4),七月/10000) 七月,
        Convert(decimal(18,4),八月/10000) 八月, Convert(decimal(18,4),九月/10000) 九月, Convert(decimal(18,4),十月/10000) 十月, Convert(decimal(18,4),十一月/10000) 十一月,
                Convert(decimal(18,4),十二月/10000) 十二月 FROM T扩展分析各业务经营月度汇总 where guid='" + guid + "' and 年类型='去年'";

        sql += @"union all
        SELECT 事业部, 部门, 分类,代理商名称, 销售员, '去年' 年类型, Convert(decimal(18,4),0) 一月, Convert(decimal(18,4),0) 二月, Convert(decimal(18,4),0) 三月, Convert(decimal(18,4),0) 四月, Convert(decimal(18,4),0) 五月, Convert(decimal(18,4),0) 六月, Convert(decimal(18,4),0) 七月,
        Convert(decimal(18,4),0) 八月, Convert(decimal(18,4),0) 九月, Convert(decimal(18,4),0) 十月, Convert(decimal(18,4),0) 十一月,
                Convert(decimal(18,4),0) 十二月
                FROM T扩展分析各业务经营月度汇总 AS a
                WHERE 年类型 = '今年'
                AND NOT EXISTS (
                    SELECT 1
                    FROM T扩展分析各业务经营月度汇总 AS b
                    WHERE b.年类型 = '去年'
                    AND b.事业部 = a.事业部
                    AND b.部门 = a.部门
                    AND b.代理商名称 = a.代理商名称
                    AND b.销售员 = a.销售员 and guid='" + guid + "') and guid='" + guid + "'";
        DataTable dataTable去年 = _SqlSugarScope.Ado.GetDataTable(sql);
        // this.GridView2.DataSource = dataTable去年;
        // ViewState["dataTable去年"] = dataTable去年;
        // this.GridView2.DataBind();
        // DataTableDynamicContextDetail = new DataTableDynamicContext(dataTable去年, (context, col) =>
        // {
        //     //所有列可排序
        //     col.Sortable = true;
        //     col.DefaultSort = true;
        // });
        去年筛选业务员(dataTable去年);


        sql = " update a set a.去年=b.去年金额 ";
        sql += " from T扩展分析各业务经营月度汇总 a ," +
            " (select 事业部, 部门,分类,代理商名称,  销售员,sum(isnull(去年,0)) 去年金额  ";
        sql += " FROM T扩展分析各业务经营月度汇总 where guid='" + guid + "' and 年类型='去年'  group by  事业部, 部门,分类,代理商名称,  销售员 ) b";
        sql += " where a.guid='" + guid + "' and a.年类型='今年' and a.事业部=b.事业部 and a.部门=b.部门 ";
        sql += " and a.分类=b.分类 and a.代理商名称 =b.代理商名称 and a.销售员=b.销售员";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "update T扩展分析各业务经营月度汇总 set 差异=[本年]-[去年] where guid='" + guid + "' and 年类型='今年'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);

        sql = "update T扩展分析各业务经营月度汇总 set 差异率=[差异]/[去年] where guid='" + guid + "' and 年类型='今年' and 去年 > 0";
        _SqlSugarScope.Ado.ExecuteCommand(sql);


        sql = @"SELECT   事业部, 部门, 分类,代理商名称, 销售员, 年类型, Convert(decimal(18,4),一月/10000) 一月, Convert(decimal(18,4),二月/10000) 二月, Convert(decimal(18,4),三月/10000) 三月, Convert(decimal(18,4),四月/10000) 四月, Convert(decimal(18,4),五月/10000) 五月, Convert(decimal(18,4),六月/10000) 六月, Convert(decimal(18,4),七月/10000) 七月,
        Convert(decimal(18,4),八月/10000) 八月, Convert(decimal(18,4),九月/10000) 九月, Convert(decimal(18,4),十月/10000) 十月, Convert(decimal(18,4),十一月/10000) 十一月,
                Convert(decimal(18,4),十二月/10000) 十二月, Convert(decimal(18,4),本年/10000) 本年, Convert(decimal(18,4),去年/10000) 去年, Convert(decimal(18,4),差异/10000) 差异, 差异率
        FROM T扩展分析各业务经营月度汇总 where guid='" + guid + "' and 年类型='今年'";

        sql += @"union all 
                 SELECT 事业部, 部门, 分类,代理商名称, 销售员,'今年' 年类型,Convert(decimal(18,4),0) 一月, Convert(decimal(18,4),0) 二月, Convert(decimal(18,4),0) 三月, Convert(decimal(18,4),0) 四月, Convert(decimal(18,4),0) 五月, Convert(decimal(18,4),0) 六月, Convert(decimal(18,4),0) 七月,
        Convert(decimal(18,4),0) 八月, Convert(decimal(18,4),0) 九月, Convert(decimal(18,4),0) 十月, Convert(decimal(18,4),0) 十一月,
                Convert(decimal(18,4),0) 十二月, Convert(decimal(18,4),isnull(本年,0)/10000) 本年, Convert(decimal(18,4),去年/10000) 去年,Convert(decimal(18,4),(isnull(本年,0) - 去年)/10000) 差异, (isnull(本年,0) - 去年)/[去年] 差异率
                FROM T扩展分析各业务经营月度汇总 AS a
                WHERE 年类型 = '去年'
                AND NOT EXISTS (
                    SELECT 1
                    FROM T扩展分析各业务经营月度汇总 AS b
                    WHERE b.年类型 = '今年'
                    AND b.事业部 = a.事业部
                    AND b.部门 = a.部门
                    AND b.代理商名称 = a.代理商名称
                    AND b.销售员 = a.销售员 and guid='" + guid + "') and guid='" + guid + "'";
        DataTable dataTable今年 = _SqlSugarScope.Ado.GetDataTable(sql);
        // this.GridView1.DataSource = dataTable今年;
        // ViewState["dataTable今年"] = dataTable今年;
        // this.GridView1.DataBind();

        // DataTableDynamicContext = new DataTableDynamicContext(dataTable今年, (context, col) =>
        // {
        //     //所有列可排序
        //     col.Sortable = true;
        //     col.DefaultSort = true;
        // });
        今年筛选业务员(dataTable今年);

        ///差异
        sql = "select 事业部, 部门, 分类, 代理商名称, 销售员,'差异' 年类型,Convert(decimal(18,4),sum(一月)/10000,4) 一月,Convert(decimal(18,4),sum(二月)/10000,4) 二月,Convert(decimal(18,4),sum(三月)/10000,4) 三月," +
            "Convert(decimal(18,4),sum(四月)/10000,4) 四月,Convert(decimal(18,4),sum(五月)/10000,4) 五月,Convert(decimal(18,4),sum(六月)/10000,4) 六月,Convert(decimal(18,4),sum(七月)/10000,4) 七月,Convert(decimal(18,4),sum(八月)/10000,4) 八月,Convert(decimal(18,4),sum(九月)/10000,4) 九月," +
            "Convert(decimal(18,4),sum(十月)/10000,4) 十月,Convert(decimal(18,4),sum(十一月)/10000,4) 十一月,Convert(decimal(18,4),sum(十二月)/10000,4) 十二月 from ";
        sql += "( SELECT     事业部, 部门, 分类, 代理商名称, 销售员, 年类型, 一月, 二月, 三月, 四月, 五月, 六月, 七月, 八月, 九月, 十月, 十一月, 十二月";
        sql += " FROM      T扩展分析各业务经营月度汇总 WHERE guid='" + guid + "' and  (年类型 = '今年') ";
        sql += " UNION ALL ";
        sql += " SELECT     事业部, 部门, 分类, 代理商名称, 销售员, 年类型, 一月 * - 1 AS Expr1, 二月 * - 1 AS Expr2, 三月 * - 1 AS Expr3,四月 * - 1 AS Expr4, ";
        sql += " 五月 * - 1 AS Expr5, 六月 * - 1 AS Expr6, 七月 * - 1 AS Expr7, 八月 * - 1 AS Expr8, 九月 * - 1 AS Expr9, ";
        sql += " 十月 * - 1 AS Expr10, 十一月 * - 1 AS Expr11, 十二月 * - 1 AS Expr12";
        sql += " FROM      T扩展分析各业务经营月度汇总  WHERE guid='" + guid + "' and (年类型 = '去年')) t " +
            " group by 事业部, 部门, 分类, 代理商名称, 销售员";
        DataTable dataTable差异 = _SqlSugarScope.Ado.GetDataTable(sql);
        // this.GridView3.DataSource = dataTable差异;
        // ViewState["dataTable差异"] = dataTable差异;
        // this.GridView3.DataBind();
        // DataTableDynamicContextDetail2 = new DataTableDynamicContext(dataTable差异, (context, col) =>
        // {
        //     //所有列可排序
        //     col.Sortable = true;
        //     col.DefaultSort = true;
        // });
        差异筛选业务员(dataTable差异);

        sql = " delete T扩展分析各业务经营月度汇总 where guid='" + guid + "'";
        _SqlSugarScope.Ado.ExecuteCommand(sql);
        #endregion
        await ToastService.Information("提示", "查询成功");
    }

    private void 今年筛选业务员(DataTable dt)
    {
        //筛选业务员
        if (dt != null && dt.Rows.Count > 0)
        {
            
            if (选中查询列 != null && string.IsNullOrEmpty(选中查询列) == false)
            {
                string[] arr = 选中查询列.Split(",");
                string groupby = "";
                var groupedQuery = dt.AsEnumerable().GroupBy(row => new
                {
                    AgentName = arr.Contains("代理商名称") ? row.Field<string>("代理商名称") : null,
                    Department = arr.Contains("部门") ? row.Field<string>("部门") : null,
                    SalesPerson = arr.Contains("销售员") ? row.Field<string>("销售员") : null,
                    BusinessUnit = arr.Contains("事业部") ? row.Field<string>("事业部") : null,
                    Classification = arr.Contains("分类") ? row.Field<string>("分类") : null,

                })
                   .Select(group => new
                   {
                       Key = group.Key,
                       一月 = group.Sum(r => r.Field<decimal>("一月")),
                       二月 = group.Sum(r => r.Field<decimal>("二月")),
                       三月 = group.Sum(r => r.Field<decimal>("三月")),
                       四月 = group.Sum(r => r.Field<decimal>("四月")),
                       五月 = group.Sum(r => r.Field<decimal>("五月")),
                       六月 = group.Sum(r => r.Field<decimal>("六月")),
                       七月 = group.Sum(r => r.Field<decimal>("七月")),
                       八月 = group.Sum(r => r.Field<decimal>("八月")),
                       九月 = group.Sum(r => r.Field<decimal>("九月")),
                       十月 = group.Sum(r => r.Field<decimal>("十月")),
                       十一月 = group.Sum(r => r.Field<decimal>("十一月")),
                       十二月 = group.Sum(r => r.Field<decimal>("十二月")),
                       //本年, 去年, 差异, 差异率
                       本年 = group.Sum(r => r.Field<decimal?>("本年")),
                       去年 = group.Sum(r => r.Field<decimal?>("去年")),
                       差异 = group.Sum(r => r.Field<decimal?>("差异")),
                       差异率 = group.Sum(r => r.Field<decimal?>("去年")) > 0 ? group.Sum(r => r.Field<decimal?>("差异")) / group.Sum(r => r.Field<decimal?>("去年")) : 0,

                   });
                // 显示结果
                // 创建结果DataTable
                dt = new DataTable();
                if (arr.Contains("事业部")) dt.Columns.Add("事业部", typeof(string));
                if (arr.Contains("部门")) dt.Columns.Add("部门", typeof(string));
                if (arr.Contains("分类")) dt.Columns.Add("分类", typeof(string));
                if (arr.Contains("代理商名称")) dt.Columns.Add("代理商名称", typeof(string));
                if (arr.Contains("销售员")) dt.Columns.Add("销售员", typeof(string));

                dt.Columns.Add("年类型", typeof(string));
                dt.Columns.Add("一月", typeof(decimal));
                dt.Columns.Add("二月", typeof(decimal));
                dt.Columns.Add("三月", typeof(decimal));
                dt.Columns.Add("四月", typeof(decimal));
                dt.Columns.Add("五月", typeof(decimal));
                dt.Columns.Add("六月", typeof(decimal));
                dt.Columns.Add("七月", typeof(decimal));
                dt.Columns.Add("八月", typeof(decimal));
                dt.Columns.Add("九月", typeof(decimal));
                dt.Columns.Add("十月", typeof(decimal));
                dt.Columns.Add("十一月", typeof(decimal));
                dt.Columns.Add("十二月", typeof(decimal));
                //本年, 去年, 差异, 差异率
                dt.Columns.Add("本年", typeof(decimal));
                dt.Columns.Add("去年", typeof(decimal));
                dt.Columns.Add("差异", typeof(decimal));
                dt.Columns.Add("差异率", typeof(decimal));

                // 填充结果DataTable
                foreach (var group in groupedQuery)
                {
                    DataRow row = dt.NewRow();
                    if (arr.Contains("代理商名称")) row["代理商名称"] = group.Key.AgentName;
                    if (arr.Contains("部门")) row["部门"] = group.Key.Department;
                    if (arr.Contains("销售员")) row["销售员"] = group.Key.SalesPerson;
                    if (arr.Contains("事业部")) row["事业部"] = group.Key.BusinessUnit;
                    if (arr.Contains("分类")) row["分类"] = group.Key.Classification;

                    row["年类型"] = "今年";
                    row["一月"] = group.一月;
                    row["二月"] = group.二月;
                    row["三月"] = group.三月;
                    row["四月"] = group.四月;
                    row["五月"] = group.五月;
                    row["六月"] = group.六月;
                    row["七月"] = group.七月;
                    row["八月"] = group.八月;
                    row["九月"] = group.九月;
                    row["十月"] = group.十月;
                    row["十一月"] = group.十一月;
                    row["十二月"] = group.十二月;
                    //本年, 去年, 差异, 差异率
                    row["本年"] = group.本年;
                    row["去年"] = group.去年;
                    row["差异"] = group.差异;
                    row["差异率"] = group.差异率;
                    dt.Rows.Add(row);
                }
               
            }

            //修改列名
            if (dt.Columns.Contains("代理商名称"))
            {
                dt.Columns["代理商名称"].ColumnName = "客户名称";
            }

            if (dt.Columns.Contains("销售员"))
            {
                dt.Columns["销售员"].ColumnName = "业务员";
            }
            //找出对应选中的销售员
            if (选中业务员InStr != "")
            {
                var rows = dt.Select("业务员 in ('" + 选中业务员InStr + "')");
                if (rows != null && rows.Length > 0)
                {
                    dt = rows.CopyToDataTable();
                }
                else
                {
                    dt.Rows.Clear();
                }
            }
            if (dt.Columns.Contains("序号") == false)
            {
                dt.Columns.Add("序号", typeof(int)).SetOrdinal(0);
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["序号"] = i + 1;
            }
            DataTableDynamicContext = new DataTableDynamicContext(dt, (context, col) =>
                                                                           {
                                                                               //所有列可排序
                                                                               col.Sortable = true;
                                                                               col.DefaultSort = true;
                                                                               if (col.GetFieldName() == "差异率")
                                                                               {
                                                                                   col.Width = 80;
                                                                                   col.FormatString = "P2";
                                                                               }
                                                                               else if (col.GetFieldName() == "序号")
                                                                               {
                                                                                   col.Width = 40;
                                                                               }
                                                                               else if (col.GetFieldName() == "客户名称")
                                                                               {
                                                                                   col.Width = 120;
                                                                                   // col.TextEllipsis = true;
                                                                               }
                                                                               else if (col.GetFieldName() == "分类")
                                                                               {
                                                                                   col.Width = 40;
                                                                               }
                                                                               else
                                                                               {
                                                                                   col.Width = 80;
                                                                               }
                                                                           });
        }

    }

    private void 去年筛选业务员(DataTable dt)
    {
        //筛选业务员
        if (dt != null && dt.Rows.Count > 0)
        {
            
            if (选中查询列 != null && string.IsNullOrEmpty(选中查询列) == false)
            {
                string[] arr = 选中查询列.Split(",");
                string groupby = "";
                var groupedQuery = dt.AsEnumerable().GroupBy(row => new
                {
                    AgentName = arr.Contains("代理商名称") ? row.Field<string>("代理商名称") : null,
                    Department = arr.Contains("部门") ? row.Field<string>("部门") : null,
                    SalesPerson = arr.Contains("销售员") ? row.Field<string>("销售员") : null,
                    BusinessUnit = arr.Contains("事业部") ? row.Field<string>("事业部") : null,
                    Classification = arr.Contains("分类") ? row.Field<string>("分类") : null,

                })
                   .Select(group => new
                   {
                       Key = group.Key,
                       一月 = group.Sum(r => r.Field<decimal>("一月")),
                       二月 = group.Sum(r => r.Field<decimal>("二月")),
                       三月 = group.Sum(r => r.Field<decimal>("三月")),
                       四月 = group.Sum(r => r.Field<decimal>("四月")),
                       五月 = group.Sum(r => r.Field<decimal>("五月")),
                       六月 = group.Sum(r => r.Field<decimal>("六月")),
                       七月 = group.Sum(r => r.Field<decimal>("七月")),
                       八月 = group.Sum(r => r.Field<decimal>("八月")),
                       九月 = group.Sum(r => r.Field<decimal>("九月")),
                       十月 = group.Sum(r => r.Field<decimal>("十月")),
                       十一月 = group.Sum(r => r.Field<decimal>("十一月")),
                       十二月 = group.Sum(r => r.Field<decimal>("十二月")),

                   });
                // 显示结果
                // 创建结果DataTable
                dt = new DataTable();
                if (arr.Contains("事业部")) dt.Columns.Add("事业部", typeof(string));
                if (arr.Contains("部门")) dt.Columns.Add("部门", typeof(string));
                if (arr.Contains("分类")) dt.Columns.Add("分类", typeof(string));
                if (arr.Contains("代理商名称")) dt.Columns.Add("代理商名称", typeof(string));
                if (arr.Contains("销售员")) dt.Columns.Add("销售员", typeof(string));
                dt.Columns.Add("年类型", typeof(string));
                dt.Columns.Add("一月", typeof(decimal));
                dt.Columns.Add("二月", typeof(decimal));
                dt.Columns.Add("三月", typeof(decimal));
                dt.Columns.Add("四月", typeof(decimal));
                dt.Columns.Add("五月", typeof(decimal));
                dt.Columns.Add("六月", typeof(decimal));
                dt.Columns.Add("七月", typeof(decimal));
                dt.Columns.Add("八月", typeof(decimal));
                dt.Columns.Add("九月", typeof(decimal));
                dt.Columns.Add("十月", typeof(decimal));
                dt.Columns.Add("十一月", typeof(decimal));
                dt.Columns.Add("十二月", typeof(decimal));

                // 填充结果DataTable
                foreach (var group in groupedQuery)
                {
                    DataRow row = dt.NewRow();
                    if (arr.Contains("代理商名称")) row["代理商名称"] = group.Key.AgentName;
                    if (arr.Contains("部门")) row["部门"] = group.Key.Department;
                    if (arr.Contains("销售员")) row["销售员"] = group.Key.SalesPerson;
                    if (arr.Contains("事业部")) row["事业部"] = group.Key.BusinessUnit;
                    if (arr.Contains("分类")) row["分类"] = group.Key.Classification;
                    row["年类型"] = "去年";
                    row["一月"] = group.一月;
                    row["二月"] = group.二月;
                    row["三月"] = group.三月;
                    row["四月"] = group.四月;
                    row["五月"] = group.五月;
                    row["六月"] = group.六月;
                    row["七月"] = group.七月;
                    row["八月"] = group.八月;
                    row["九月"] = group.九月;
                    row["十月"] = group.十月;
                    row["十一月"] = group.十一月;
                    row["十二月"] = group.十二月;
                    dt.Rows.Add(row);
                }
            }

            //修改列名
            if (dt.Columns.Contains("代理商名称"))
            {
                dt.Columns["代理商名称"].ColumnName = "客户名称";
            }

            if (dt.Columns.Contains("销售员"))
            {
                dt.Columns["销售员"].ColumnName = "业务员";
            }
            //找出对应选中的销售员
            if (选中业务员InStr != "")
            {
                var rows = dt.Select("业务员 in ('" + 选中业务员InStr + "')");
                if (rows != null && rows.Length > 0)
                {
                    dt = rows.CopyToDataTable();
                }
                else
                {
                    dt.Rows.Clear();
                }
            }


            if (dt.Columns.Contains("序号") == false)
            {
                dt.Columns.Add("序号", typeof(int)).SetOrdinal(0);
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["序号"] = i + 1;
            }

            DataTableDynamicContextDetail = new DataTableDynamicContext(dt, (context, col) =>
                                                                           {
                                                                               //所有列可排序
                                                                               col.Sortable = true;
                                                                               col.DefaultSort = true;

                                                                               if (col.GetFieldName() == "客户名称")
                                                                               {
                                                                                   col.Width = 120;
                                                                                   // col.TextEllipsis = true;
                                                                               }
                                                                               else if (col.GetFieldName() == "序号")
                                                                               {
                                                                                   col.Width = 40;
                                                                               }
                                                                               else if (col.GetFieldName() == "分类")
                                                                               {
                                                                                   col.Width = 40;
                                                                               }
                                                                               else
                                                                               {
                                                                                   col.Width = 80;
                                                                               }
                                                                           });
        }
        
    }

    private void 差异筛选业务员(DataTable dt)
    {
        //筛选业务员
        if (dt != null && dt.Rows.Count > 0)
        {
            

            if (选中查询列 != null && string.IsNullOrEmpty(选中查询列) == false)
            {
                string[] arr = 选中查询列.Split(",");
                string groupby = "";
                var groupedQuery = dt.AsEnumerable().GroupBy(row => new
                {
                    AgentName = arr.Contains("代理商名称") ? row.Field<string>("代理商名称") : null,
                    Department = arr.Contains("部门") ? row.Field<string>("部门") : null,
                    SalesPerson = arr.Contains("销售员") ? row.Field<string>("销售员") : null,
                    BusinessUnit = arr.Contains("事业部") ? row.Field<string>("事业部") : null,
                    Classification = arr.Contains("分类") ? row.Field<string>("分类") : null,
                  
                })
                   .Select(group => new
                   {
                       Key = group.Key,
                       一月 = group.Sum(r => r.Field<decimal>("一月")),
                       二月 = group.Sum(r => r.Field<decimal>("二月")),
                       三月 = group.Sum(r => r.Field<decimal>("三月")),
                       四月 = group.Sum(r => r.Field<decimal>("四月")),
                       五月 = group.Sum(r => r.Field<decimal>("五月")),
                       六月 = group.Sum(r => r.Field<decimal>("六月")),
                       七月 = group.Sum(r => r.Field<decimal>("七月")),
                       八月 = group.Sum(r => r.Field<decimal>("八月")),
                       九月 = group.Sum(r => r.Field<decimal>("九月")),
                       十月 = group.Sum(r => r.Field<decimal>("十月")),
                       十一月 = group.Sum(r => r.Field<decimal>("十一月")),
                       十二月 = group.Sum(r => r.Field<decimal>("十二月")),
                      
                   });
                // 显示结果
                // 创建结果DataTable
                dt = new DataTable();
                if (arr.Contains("事业部")) dt.Columns.Add("事业部", typeof(string));
                if (arr.Contains("部门")) dt.Columns.Add("部门", typeof(string));
                if (arr.Contains("分类")) dt.Columns.Add("分类", typeof(string));
                if (arr.Contains("代理商名称")) dt.Columns.Add("代理商名称", typeof(string));
                if (arr.Contains("销售员")) dt.Columns.Add("销售员", typeof(string));

                dt.Columns.Add("年类型", typeof(string));
                dt.Columns.Add("一月", typeof(decimal));
                dt.Columns.Add("二月", typeof(decimal));
                dt.Columns.Add("三月", typeof(decimal));
                dt.Columns.Add("四月", typeof(decimal));
                dt.Columns.Add("五月", typeof(decimal));
                dt.Columns.Add("六月", typeof(decimal));
                dt.Columns.Add("七月", typeof(decimal));
                dt.Columns.Add("八月", typeof(decimal));
                dt.Columns.Add("九月", typeof(decimal));
                dt.Columns.Add("十月", typeof(decimal));
                dt.Columns.Add("十一月", typeof(decimal));
                dt.Columns.Add("十二月", typeof(decimal));

                // 填充结果DataTable
                foreach (var group in groupedQuery)
                {
                    DataRow row = dt.NewRow();
                    if (arr.Contains("代理商名称")) row["代理商名称"] = group.Key.AgentName;
                    if (arr.Contains("部门")) row["部门"] = group.Key.Department;
                    if (arr.Contains("销售员")) row["销售员"] = group.Key.SalesPerson;
                    if (arr.Contains("事业部")) row["事业部"] = group.Key.BusinessUnit;
                    if (arr.Contains("分类")) row["分类"] = group.Key.Classification;

                    row["年类型"] = "差异";
                    row["一月"] = group.一月;
                    row["二月"] = group.二月;
                    row["三月"] = group.三月;
                    row["四月"] = group.四月;
                    row["五月"] = group.五月;
                    row["六月"] = group.六月;
                    row["七月"] = group.七月;
                    row["八月"] = group.八月;
                    row["九月"] = group.九月;
                    row["十月"] = group.十月;
                    row["十一月"] = group.十一月;
                    row["十二月"] = group.十二月;
                    dt.Rows.Add(row);
                }
            }


            //修改列名
            if (dt.Columns.Contains("代理商名称"))
            {
                dt.Columns["代理商名称"].ColumnName = "客户名称";
            }

            if (dt.Columns.Contains("销售员"))
            {
                dt.Columns["销售员"].ColumnName = "业务员";
            }

            //找出对应选中的销售员
            if (选中业务员InStr != "")
            {
                var rows = dt.Select("业务员 in ('" + 选中业务员InStr + "')");
                if (rows != null && rows.Length > 0)
                {
                    dt = rows.CopyToDataTable();
                }
                else
                {
                    dt.Rows.Clear();
                }
            }
            if (dt.Columns.Contains("序号") == false)
            {
                dt.Columns.Add("序号", typeof(int)).SetOrdinal(0);
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["序号"] = i + 1;
            }

            DataTableDynamicContextDetail2 = new DataTableDynamicContext(dt, (context, col) =>
                                                                           {
                                                                               //所有列可排序
                                                                               col.Sortable = true;
                                                                               col.DefaultSort = true;
                                                                               if (col.GetFieldName() == "客户名称")
                                                                               {
                                                                                   col.Width = 120;
                                                                                   // col.TextEllipsis = true;
                                                                               }
                                                                               else if (col.GetFieldName() == "序号")
                                                                               {
                                                                                   col.Width = 40;
                                                                               }
                                                                               else if (col.GetFieldName() == "分类")
                                                                               {
                                                                                   col.Width = 40;
                                                                               }
                                                                               else
                                                                               {
                                                                                   col.Width = 80;
                                                                               }
                                                                           });
        }

    }
}
